﻿
IF OBJECT_ID('up_KML_AssetDistributionPoles') IS not NULL
	DROP PROC up_KML_AssetDistributionPoles 

GO 

-- [up_UtilityAssetDistribution_SelectForKML] 4

CREATE PROCEDURE [dbo].[up_KML_AssetDistributionPoles]
	@id int
AS

DECLARE @DistributionPolesList nvarchar(max)
SET @DistributionPolesList = ''

SELECT 
	 @DistributionPolesList = @DistributionPolesList + CONVERT(varchar, [Utility_Asset_DistributionId]) + ','
FROM 
	v_tb_Utility_Asset_Distributions
WHERE
	UtilityFK = @id


declare @settingValue varchar(500)
set @settingValue = (select SettingValue from tb_Settings where SettingId = 26)

declare @template varchar(max);
set @template = '
	<iframe 
		src="{url}{page}?ID=$[ID]" 
		style="width: 325px; height: 400px;"
		frameborder="0"  marginwidth="0" marginheight="0">Pole Position</iframe>
		$[description]'
select @template = replace(@template, '{url}', SettingValue)
from tb_Settings where SettingId = 23

declare @xml varchar(max);
select @xml = replace(convert(varchar(max),
(select 

	 'Pole Position Desktop' as 'Document/name'

	-- style info

	,(select
		'AssetDistribution' as '@id'
		,.75 as 'IconStyle/scale'
		-- brown
		,'ff0055aa' as 'IconStyle/color'
		,'5' as 'LineStyle/width'
		,replace(@template, '{page}', 'AssetDistribution.aspx') as 'BalloonStyle/text'
		,Replace(@settingValue, '{0}', 'mapicon_asset_distribution_pole.png') as 'IconStyle/Icon/href'
		for xml path('Style'), type) as 'Document'

	-- point: Distribution Pole (Asset) 

		
		,(select 
			-- what test shold show up on the map?
			List.Value as 'name'

			-- all assets of this type get the same base style
			,'#AssetDistribution' as 'styleUrl'

			-- should we show a special icon?
			, case Phases.PhaseFk
				when 1 then Replace(@settingValue, '{0}', 'phase1.png')
				when 2 then Replace(@settingValue, '{0}', 'phase2.png')
				when 3 then Replace(@settingValue, '{0}', 'phase3.png')
				when 4 then Replace(@settingValue, '{0}', 'doubleciruit.png')
			  end as 'Style/IconStyle/Icon/href'
			-- should we show it in a special color?
			--,@DistributionPolesColor as 'Style/IconStyle/color'
			,'ID' as 'ExtendedData/Data/@name'
			,List.Value as 'ExtendedData/Data/value'

			-- where is it?
			,Convert(varchar, Longitude, 128) + ',' + Convert(varchar, Latitude, 128) + ',0' as 'Point/coordinates'
		from
			(select * from dbo.fn_Split(@DistributionPolesList, default)) as List
			 join tb_Utility_Asset_Distributions as Poles
				on Poles.Utility_Asset_DistributionId = Convert(int, List.Value)
			 left join tb_Distributions_TopTypes as TopTypes
				on TopTypes.Distributions_TopTypeId = Poles.Distribution_TopTypeFk
			 left join tb_Utility_Asset_Distribution_PhasesXPole as Phases
				on Phases.Utility_Asset_DistributionFk = Poles.Utility_Asset_DistributionId
			for xml path('Placemark'), TYPE
		)  as 'Document'	
	
for xml path('kml'))), '<kml>', '<?xml version="1.0" encoding="UTF-8"?><kml xmlns="http://earth.google.com/kml/2.2">')

--select convert(xml, @xml);
select @xml;





 